package com.szholly.pro.watergas_server.common.data;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map.Entry;
import java.util.Set;
import java.util.UUID;

import javax.servlet.http.HttpServletRequest;

import com.szholly.plug.form.validation.ValidationEditInterceptor;
import com.szholly.pro.watergas_server.common.util.JsonUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

import com.szholly.utils.spring.SpringBeanFactory;
import com.szholly.utils.util.DateUtils;
import com.szholly.utils.util.StringUtils;
import com.szholly.data.common.util.ConvertCaptionUtils;
import com.szholly.data.common.util.ValueConvertUtils;
import com.szholly.data.general.Field;
import com.szholly.data.general.GlobalDataType;
import com.szholly.data.general.GlobalParameter;
import com.szholly.data.general.ITable;
import com.szholly.data.general.ITableFactory;
import com.szholly.data.general.IWorkspace;
import com.szholly.data.general.IWorkspaceFactory;
import com.szholly.data.general.MoreToMoreRelationInfo;
import com.szholly.data.general.PageInfo;
import com.szholly.data.general.RelationInfo;
import com.szholly.data.general.RelationNode;
import com.szholly.data.general.RowBase;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

/**
 * 数据操作工具类
 * @author gislin
 * 2015年12月21日
 */
public class DataUtil {

	private final static Logger logger = LoggerFactory.getLogger(DataUtil.class);
	
	public final static String CACHE = "CACHE";



	@Autowired
	private static SpringBeanFactory springBeanFactory;
	
	/**
	 * 分页数据查询
	 * gislin
	 * 2015年12月22日
	 * @param sql
	 * @param parms
	 * @return
	 * @throws Exception
	 */
	public static String getPageSelectJson(String sql, List<GlobalParameter> parms) throws Exception {
		JSONObject resultObject = getPageSelectJsonObject(sql, parms);
		String jsonStr = resultObject.toString();
		return jsonStr;
	}

	/**
	 * 分页数据查询
	 * gislin
	 * 2015年12月22日
	 * @param sql
	 * @param parms
	 * @param table
	 * @return
	 * @throws Exception
	 */
	public static String getPageSelectJson(String sql, List<GlobalParameter> parms, ITable table) throws Exception {
		JSONObject resultObject = getPageSelectJsonObject(sql, parms, table);
		String jsonStr = resultObject.toString();
		return jsonStr;
	}
	
	/**
	 * 分页数据查询，返回json字符串，并根据tablename翻译字典
	 * gislin
	 * 2015年12月22日
	 * @param sql
	 * @param parms
	 * @param tableName
	 * @return
	 * @throws Exception
	 */
	public static String getPageSelectJson(String sql, List<GlobalParameter> parms, String tableName) throws Exception {
		ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
		ITable pTable = tableFactory.openTable(tableName);
		JSONObject resultObject = getPageSelectJsonObject(sql, parms, pTable);
		String jsonStr = resultObject.toString();
		return jsonStr;
	}
	
	/**
	 * 分页数据查询
	 * gislin
	 * 2015年12月22日
	 * @param sql
	 * @param parms
	 * @param table
	 * @return
	 * @throws Exception
	 */
	public static List<RowBase> getPageList(String sql, List<GlobalParameter> parms, ITable table) throws Exception {
		PageInfo pageInfo = getPageInfo();

		List<RowBase> list = getPageRow(sql, parms,table, pageInfo);

		return list;
	}

	/**
	 * 分页数据查询，返回json对象
	 * gislin
	 * 2015年12月22日
	 * @param sql
	 * @param parms
	 * @return
	 * @throws Exception
	 */
	public static JSONObject getPageSelectJsonObject(String sql, List<GlobalParameter> parms) throws Exception {
		JSONObject resultObject = getPageSelectJsonObject(sql, parms, null);
		return resultObject;
	}
	
	/**
	 * 分页获取数据，返回带分页信息的结果
	 * gislin
	 * 2016年8月10日
	 * @param sql
	 * @param parms
	 * @param table
	 * @return
	 * @throws Exception
	 */
	public static ListPageResult getPageListResult(String sql, List<GlobalParameter> parms, ITable table) throws Exception {
		PageInfo pageInfo = getPageInfo();
		ListPageResult result = getPageListResult(sql, parms, table,pageInfo);
		return result;
	}
	
	/**
	 * 分页获取数据，返回带分页信息的结果
	 * gislin
	 * 2016年8月10日
	 * @param sql
	 * @param parms
	 * @param table
	 * @return
	 * @throws Exception
	 */
	public static ListPageResult getPageListResult(String sql, List<GlobalParameter> parms, ITable table,PageInfo pageInfo) throws Exception {

		List<RowBase> list = getPageRow(sql, parms,table, pageInfo);
		ListPageResult result = new ListPageResult(list, pageInfo);
	
		return result;
	}

	/**
	 * 分页数据查询，并根据table对象翻译字典，返回json对象
	 * gislin
	 * 2015年12月22日
	 * @param sql
	 * @param parms
	 * @param table
	 * @return
	 * @throws Exception
	 */
	public static JSONObject getPageSelectJsonObject(String sql, List<GlobalParameter> parms, ITable table)
			throws Exception {

		PageInfo pageInfo = getPageInfo();

		List<RowBase> list = getPageRow(sql, parms,table, pageInfo);
		return listToJSONObject(list,null,pageInfo);
	}

	/**
	 * 分页数据查询
	 * gislin
	 * 2015年12月22日
	 * @param sql
	 * @param parms
	 * @param table
	 * @param pageInfo
	 * @return
	 * @throws Exception
	 */
	public static List<RowBase> getPageRow(String sql, List<GlobalParameter> parms,ITable table, PageInfo pageInfo) throws Exception {
		
		boolean needCache = false;
		
		if(sql.startsWith(DataUtil.CACHE)){
			sql = sql.replaceFirst(DataUtil.CACHE,"");
			needCache = true;
		}
		
		if(pageInfo == null){
			pageInfo = new PageInfo();
			pageInfo.setPageIndex(1);
			pageInfo.setPageSize(10);
		}

		List<RowBase> list = getCache(sql, parms, pageInfo);
		
		// 如果缓存中有数据，直接获取返回
		if(list != null){
			logger.info("从缓存中获取数据："+sql);
			return list;
		}
		
		GlobalParameter[] parmArray = new GlobalParameter[0];
		if (parms != null) {
			parmArray = parms.toArray(new GlobalParameter[parms.size()]);
		}
		
		IWorkspaceFactory workspaceFactory = (IWorkspaceFactory) SpringBeanFactory.getBean("WorkspaceFactory");
		IWorkspace pWorkspace = workspaceFactory.openWorkspace();

		try {
			int pageIndex = pageInfo.getPageIndex();
			int pageSize = pageInfo.getPageSize();
			
			int count = 0;
			int pageCount = 0;
			int offset = (pageIndex - 1) * pageSize;
			String sqlCount = " SELECT COUNT(*) FROM (" + sql + ") t";
			count = ((BigDecimal) pWorkspace.getExecuteScalar(sqlCount, parmArray)).intValue();

			pageCount = (int) Math.ceil(count * 1.0 / pageSize);
			pageIndex = pageIndex > pageCount ? pageCount : pageIndex;
			pageInfo.setPageCount(pageCount);
			pageInfo.setPageIndex(pageIndex);
			pageInfo.setRowCount(count);

			String executeSql = " SELECT * FROM ( SELECT t.*,ROWNUM oracle_rn FROM (" + sql + " ) t where ROWNUM <= "
					+ (pageSize + offset) + " ) t1 WHERE oracle_rn > " + offset;
			
			logger.info(executeSql);
			list = pWorkspace.getEntityList(RowBase.class, executeSql, parmArray);

		} catch (Exception e) {
			throw e;
		}

		if(needCache){
			addCache(sql, parms, pageInfo, list);
		}
		
		if (table != null) {
			ConvertCaptionUtils.convertCaptionField(table, null, list);
		}
		
		return list;
	}

	/**
	 * 不分页查询结果，返回json字符串
	 * gislin
	 * 2015年12月22日
	 * @param sql
	 * @param parms
	 * @param table
	 * @return
	 */
	public static String getSelectJson(String sql, List<GlobalParameter> parms, ITable table) {
		JSONObject resultObject = getSelectJsonObject(sql, parms, table);
		return resultObject.toString();
	}

	/**
	 * 不分页查询结果，返回json对象
	 * gislin
	 * 2015年12月22日
	 * @param sql
	 * @param parms
	 * @param table
	 * @return
	 */
	public static JSONObject getSelectJsonObject(String sql, List<GlobalParameter> parms, ITable table) {
		List<RowBase> list = getSelectRows(sql,parms,table);
		return listToJSONObject(list,null,null);
	}
	
	/**
	 * 不分页查询结果
	 * gislin
	 * 2015年12月22日
	 * @param sql
	 * @param parms
	 * @return
	 */
	public static List<RowBase> getSelectRows(String sql, List<GlobalParameter> parms){
		List<RowBase> result = getSelectRows(sql, parms, null);
		return result;
	}
	
	/**
	 * 获取单列集合
	 * gislin
	 * 2016年8月26日
	 * @param sql
	 * @param parms
	 * @return
	 */
	public static List<Object> getSelectObjectRows(String sql, List<GlobalParameter> parms){
		List<Object> result = new ArrayList<>();
		List<RowBase> rows = getSelectRows(sql, parms, null);
		for(RowBase row : rows){
			result.add(row.get(0));
		}
		return result;
	}
	
	/**
	 * 获取单列字符集合
	 * gislin
	 * 2016年8月26日
	 * @param sql
	 * @param parms
	 * @return
	 */
	public static List<String> getSelectStringRows(String sql, List<GlobalParameter> parms,String fieldName){
		List<String> result = new ArrayList<>();
		List<RowBase> rows = getSelectRows(sql, parms, null);
		for(RowBase row : rows){
			Object value = row.get(fieldName);
			if(value != null){
				result.add(value.toString());
			}
		}
		return result;
	}
	
	/**
	 * 不分页查询结果
	 * gislin
	 * 2015年12月22日
	 * @param sql
	 * @param parms
	 * @param table
	 * @return
	 */
	public static List<RowBase> getSelectRows(String sql, List<GlobalParameter> parms, ITable table){
		
		long startTime=System.currentTimeMillis();   //获取开始时间
		
		boolean needCache = false;
		
		if(sql.startsWith(DataUtil.CACHE)){
			sql = sql.replaceFirst(DataUtil.CACHE,"");
			needCache = true;
		}
		
		List<RowBase> list = getCache(sql, parms, null);
		
		// 如果缓存中有数据，直接获取返回
		if(list != null){
			logger.info("从缓存中获取数据："+sql);
			return list;
		}
		
		IWorkspaceFactory workspaceFactory = (IWorkspaceFactory) SpringBeanFactory.getBean("WorkspaceFactory");
		IWorkspace pWorkspace = workspaceFactory.openWorkspace();
		GlobalParameter[] parmArray = new GlobalParameter[0];
		if (parms != null) {
			parmArray = parms.toArray(new GlobalParameter[parms.size()]);
		}
		list = pWorkspace.getEntityList(RowBase.class, sql, parmArray);

		if (table != null) {
			ConvertCaptionUtils.convertCaptionField(table, null, list);
		}
		
		if(needCache){
			addCache(sql, parms, null, list);
		}
		long endTime = System.currentTimeMillis(); //获取结束时间
		System.out.println("sql："+sql+"\r\n查询耗时： "+(endTime-startTime)+"ms");
		return list;
	}
	
	/**
	 * 获取缓存数据
	 * gislin
	 * 2015年12月22日
	 * @param sql
	 * @param params
	 * @param pageinfo
	 * @return
	 */
	public static List<RowBase> getCache(String sql,List<GlobalParameter> params,PageInfo pageinfo){
		
		if(sql.startsWith("CACHE")){
			sql = sql.replaceFirst("CACHE", "");
		}
		
		String hashCode = sql.hashCode()+"";
		
		if(params != null){
			hashCode += params.hashCode();
		}
		
		if(pageinfo != null){
			hashCode += pageinfo.getPageIndex()+"_"+pageinfo.getPageSize();
		}
		
		Object obj = DataCacheManager.getCache(hashCode);
		
		if(obj != null && obj instanceof List<?>){
			@SuppressWarnings("unchecked")
			List<RowBase> rows = (List<RowBase>)obj;
			return rows;
		}
		else{
			return null;
		}
	}
	
	/**
	 * 把数据放入缓存中
	 * gislin
	 * 2015年12月22日
	 * @param sql
	 * @param params
	 * @param pageinfo
	 * @param rows
	 */
	public static void addCache(String sql,List<GlobalParameter> params,PageInfo pageinfo,List<RowBase> rows){
		if(sql.startsWith("CACHE")){
			sql = sql.replaceFirst("CACHE", "");
		}
		
		String hashCode = sql.hashCode()+"";
		
		if(params != null){
			hashCode += params.hashCode();
		}
		
		if(pageinfo != null){
			hashCode += pageinfo.getPageIndex()+"_"+pageinfo.getPageSize();
		}
		
		DataCacheManager.addCache(hashCode, rows);
	}
	
	public static JSONObject listToJSONObject(List<RowBase> list) {
		return listToJSONObject(list,null,null);
	}
	
	public static JSONObject listToJSONObject(List<RowBase> list, String tableName) {
		ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
		ITable table = tableFactory.openTable(tableName);
		return listToJSONObject(list,table,null);
	}

	/**
	 * list转出JSON
	 * @param list 需要转换的list
	 * @param table 如table不为空，执行字典项转换
	 * @param pageInfo 如果分页信息不为空，JSON结果包括分页信息
	 * @return
	 */
	public static JSONObject listToJSONObject(List<RowBase> list, ITable table, PageInfo pageInfo) {
		if (table != null) {
			ConvertCaptionUtils.convertCaptionField(table, null, list);
		}
		JSONObject resultObject = new JSONObject();
		
		// 查询结果
		JSONArray data = JSONArray.fromObject(list, JsonUtil.getConfig());
		resultObject.put("data", data);
		
		//参数信息
		JSONArray paramInfo = new JSONArray();
		JSONObject tableNameJSON = new JSONObject();
		paramInfo.add(tableNameJSON);
		resultObject.put("paramInfo", paramInfo);
		
		//分页信息
		if(pageInfo!=null){
			resultObject.put("pageInfo", JSONObject.fromObject(pageInfo,JsonUtil.getConfig()));
		}
		return resultObject;
	}
	
	/**
	 * 把对相关转成json对象，为了统一json对象的格式，统一用工具类的方法来转换
	 * gislin
	 * 2016年1月6日
	 * @param obj
	 * @return
	 */
	public static JSONObject objectToJsonObject(Object obj){
		JSONObject resultObject = new JSONObject();
		
		// 查询结果
		JSONArray data = JSONArray.fromObject(obj, JsonUtil.getConfig());
		resultObject.put("data", data);
		return resultObject;
	}
	
	/**
	 * 获取查询的第一行第一列的结果
	 * gislin
	 * 2015年12月22日
	 * @param sql
	 * @param parms
	 * @return
	 */
	public static Object getScale(String sql, List<GlobalParameter> parms) {

		IWorkspaceFactory workspaceFactory = (IWorkspaceFactory) SpringBeanFactory.getBean("WorkspaceFactory");
		IWorkspace pWorkspace = workspaceFactory.openWorkspace();

		GlobalParameter[] parmArray = new GlobalParameter[0];
		if (parms != null) {
			parmArray = parms.toArray(new GlobalParameter[parms.size()]);
		}
		Object result = null;
		try {
			result = pWorkspace.getExecuteScalar(sql, parmArray);
		} catch (EmptyResultDataAccessException e) {
			result = null;
		}
		
		return result;
	}
	
	/**
	 * 获取查询的第一行第一列的结果，并转换成int
	 * gislin
	 * 2015年12月22日
	 * @param sql
	 * @param parms
	 * @return
	 */
	public static int getScaleInt(String sql, List<GlobalParameter> parms){
		Object result = getScale(sql, parms);
		int value ;
		try {
			value = Integer.parseInt(result+"");
		} catch (Exception e) {
			value = 0;
		}
		return value;
	}
	
	/**
	 * 获取查询的第一行第一列的结果，转换成日期
	 * gislin
	 * 2016年1月16日
	 * @param sql
	 * @param parms
	 * @return
	 */
	public static Date getScaleDate(String sql,List<GlobalParameter> parms){
		Object result = getScale(sql, parms);
		Date date = null;
		try {
			date = (Date)result;
		} catch (Exception e) {
			
		}
		return date;
	}

	public static PageInfo getPageInfo() {
		HttpServletRequest resq = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getRequest();

		String pageSize = resq.getParameter("pageSizeData");
		String pageIndex = resq.getParameter("pageIndexData");
		int pageSizeInt = 25;
		if (!StringUtils.IsNullOrSpace(pageSize)) {
			pageSizeInt = Integer.parseInt(pageSize);
		}
		int pageIndexInt = 1;
		if (!StringUtils.IsNullOrSpace(pageIndex)) {
			pageIndexInt = Integer.parseInt(pageIndex);
		}
		PageInfo pageInfo = new PageInfo();
		pageInfo.setPageIndex(pageIndexInt);
		pageInfo.setPageSize(pageSizeInt);
		pageInfo.setRefreshRowCount(true);

		return pageInfo;
	}

	public static String Create(String tableName, HashMap<String, Object> parms) {
		ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
		ITable pTable = tableFactory.openTable(tableName);
		IWorkspaceFactory workspaceFactory = (IWorkspaceFactory) SpringBeanFactory.getBean("WorkspaceFactory");
		IWorkspace pWorkspace = workspaceFactory.openWorkspace();
		String guid ;
		if (!parms.containsKey("GUID")||StringUtils.IsNullOrEmpty(parms.get("GUID"))) {
			guid = UUID.randomUUID().toString();
			parms.put("GUID", guid);
		}
		else{
			guid = parms.get("GUID") + "";
		}
		
		//加入验证逻辑，add by WINSON
		ValidationEditInterceptor.validate(pTable, parms,false);
		
		IDataInterceptor interceprot = DataInterceptorManager.getInstance().get(tableName);
		RowBase model = getModelFromParms(pTable, parms);
		if(interceprot != null){
			interceprot.beforeInsert(model);
		}
		
		try {
			beginTransaction();
			
			String sql = getInsertSql(pTable, parms);
			GlobalParameter[] parmsArray = getParameter(pTable, parms,false);
			pWorkspace.executeSql(sql, parmsArray);
			insertChildModel(pTable, parms);
			if(interceprot != null){
				interceprot.afterInsert(model);
			}
			commitTransaction();
		} catch (Exception e) {
			rollbackTransaction();
			e.printStackTrace();
		}
		return guid;
	}
	
	public static void Update(String tableName, HashMap<String, Object> parms) {
		ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
		ITable pTable = tableFactory.openTable(tableName);
		IWorkspaceFactory workspaceFactory = (IWorkspaceFactory) SpringBeanFactory.getBean("WorkspaceFactory");
		IWorkspace pWorkspace = workspaceFactory.openWorkspace();

		//加入验证逻辑，add by WINSON
		ValidationEditInterceptor.validate(pTable, parms,true);
		
		IDataInterceptor interceprot = DataInterceptorManager.getInstance().get(tableName);
		RowBase model = getModelFromParms(pTable, parms);
		if(interceprot != null){
			interceprot.beforeUpdate(model);
		}

		try {
			beginTransaction();
			String sql = getUpdateSql(pTable, parms);
			GlobalParameter[] parmsArray = getParameter(pTable, parms,true);
			pWorkspace.executeSql(sql, parmsArray);
			if(parms.containsKey("updateChild") && parms.get("updateChild").equals("true")){
				insertChildModel(pTable, parms);
			}			
			if(interceprot != null){
				interceprot.afterUpdate(model);
			}
			commitTransaction();
		} catch (Exception e) {
			rollbackTransaction();
			e.printStackTrace();
		}
	}
	
	public static void Delete(String tableName, String key) {
		ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
		ITable pTable = tableFactory.openTable(tableName);
		IWorkspaceFactory workspaceFactory = (IWorkspaceFactory) SpringBeanFactory.getBean("WorkspaceFactory");
		IWorkspace pWorkspace = workspaceFactory.openWorkspace();
				
		String sql = getDeleteSql(pTable);
		GlobalParameter[] parmsArray = new GlobalParameter[]{new GlobalParameter("key", key)};
		
		IDataInterceptor interceprot = DataInterceptorManager.getInstance().get(tableName);
		if(interceprot != null){
			interceprot.beforeDelete(key);
		}

		try {
			beginTransaction();
			pWorkspace.executeSql(sql, parmsArray);
			deleteChildModel(pTable, key);
			if(interceprot != null){
				interceprot.afterDelete(key);
			}
			commitTransaction();
		} catch (Exception e) {
			rollbackTransaction();
			e.printStackTrace();
		}
	}
	
	public static RowBase getModelByKey(String tableName,String key){
		ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
		ITable pTable = tableFactory.openTable(tableName);
		IWorkspaceFactory workspaceFactory = (IWorkspaceFactory) SpringBeanFactory.getBean("WorkspaceFactory");
		IWorkspace pWorkspace = workspaceFactory.openWorkspace();
		
		String getModelSql = getModelSql(pTable);
		GlobalParameter[] parmsArray = new GlobalParameter[]{new GlobalParameter("key", key)};
		List<RowBase> models = pWorkspace.getEntityList(RowBase.class, getModelSql, parmsArray);
		
		ConvertCaptionUtils.convertCaptionField(pTable, null, models);
		
		if(models.size() == 0){
			return null;
		}
		
		RowBase model = models.get(0);
		
		RelationNode relationNode = pTable.getRelation();
		
		if(relationNode == null || relationNode.getChildRelationNode() == null){
			return model;
		}
		
		HashMap<String, RelationInfo> relationInfoMap = relationNode.getChildRelationNode();
		
		for(Entry<String, RelationInfo> relation : relationInfoMap.entrySet()){
			String childTableName = relation.getKey();
			
			String getChildModelsSql ;
			String foreignKeyValue;
			RelationInfo relationiNFO = relation.getValue();
			String relationTableName = "";
			
			if(relationiNFO instanceof MoreToMoreRelationInfo){
				MoreToMoreRelationInfo m2mRelation = (MoreToMoreRelationInfo)relationiNFO;
				relationTableName = m2mRelation.getForeignTableName();
				getChildModelsSql = " SELECT t."+m2mRelation.getMiddleTableRightFieldName()+", t1.* FROM " + m2mRelation.getMiddleTableName() + " t \n"
						+ "	LEFT JOIN " + m2mRelation.getForeignTableName() + " t1 on t."+m2mRelation.getMiddleTableRightFieldName() + " = t1."+m2mRelation.getForeignTableFieldName()
						+ " WHERE " + m2mRelation.getMiddleTableLeftFieldName() + " = @foreignKey";
				foreignKeyValue = model.getSafeString(m2mRelation.getMatserTableFieldName());
			}
			else{
				if(relationiNFO == null || relationiNFO.getForeignkey() == null){
					continue;
				}
				relationTableName = childTableName;
				String modelFieldName = relationiNFO.getForeignkey().keySet().iterator().next();
				String foreignFieldName = relationiNFO.getForeignkey().get(modelFieldName);
				getChildModelsSql = " SELECT * FROM "+childTableName+" WHERE "+foreignFieldName + "=@foreignKey";
				foreignKeyValue = model.getSafeString(modelFieldName);
			}
			ITable childTable = tableFactory.openTable(relationTableName);
			List<GlobalParameter> childParmsArray = new ArrayList<>();
			childParmsArray.add(new GlobalParameter("foreignKey", foreignKeyValue));
			List<RowBase> childList = getSelectRows(getChildModelsSql, childParmsArray, childTable);
			model.put(childTableName+"_LIST", childList);
		}
		return model;
	}
	
	public static int ExecuteUpdateSql(String sql, List<GlobalParameter> parms) {
		IWorkspaceFactory workspaceFactory = (IWorkspaceFactory) SpringBeanFactory.getBean("WorkspaceFactory");
		IWorkspace pWorkspace = workspaceFactory.openWorkspace();
		GlobalParameter[] parmArray = new GlobalParameter[0];
		if (parms != null) {
			parmArray = parms.toArray(new GlobalParameter[parms.size()]);
		}
		int count = pWorkspace.executeSql(sql, parmArray);
		return count;
	}
	
	public static int ExecuteUpdateSqlInTransaction(String sql, List<GlobalParameter> parms) {
		IWorkspaceFactory workspaceFactory = (IWorkspaceFactory) SpringBeanFactory.getBean("WorkspaceFactory");
		IWorkspace pWorkspace = workspaceFactory.openWorkspace();
		pWorkspace.startEditing();
		GlobalParameter[] parmArray = new GlobalParameter[0];
		if (parms != null) {
			parmArray = parms.toArray(new GlobalParameter[parms.size()]);
		}
		int count = pWorkspace.executeSql(sql, parmArray);
		pWorkspace.stopEditing(true);
		return count;
	}

	public static void beginTransaction(){
		IWorkspaceFactory workspaceFactory = (IWorkspaceFactory) SpringBeanFactory.getBean("WorkspaceFactory");
		IWorkspace pWorkspace = workspaceFactory.openWorkspace();
		pWorkspace.startEditing();
	}
	
	public static void commitTransaction(){
		IWorkspaceFactory workspaceFactory = (IWorkspaceFactory) SpringBeanFactory.getBean("WorkspaceFactory");
		IWorkspace pWorkspace = workspaceFactory.openWorkspace();
		pWorkspace.stopEditing(true);
	}
	
	public static void rollbackTransaction(){
		IWorkspaceFactory workspaceFactory = (IWorkspaceFactory) SpringBeanFactory.getBean("WorkspaceFactory");
		IWorkspace pWorkspace = workspaceFactory.openWorkspace();
		pWorkspace.stopEditing(false);
	}
	
	private static String getModelSql(ITable table){
		Field[] keyFields = table.getKeyFields();
		String sql = " SELECT * FROM "+table.getTableName()+" where "+keyFields[0].getFieldName()+"=@key";
		return sql;
	}
	
	protected static String getUpdateSql(ITable pTable, HashMap<String, Object> parms) {

		String sql = "";
		String whereString = "";
		Field[] keyFields = pTable.getKeyFields();

		for (Field field : keyFields) {
			String keyName = field.getFieldName();
			whereString += "and " + keyName + " = @" + keyName;
		}
		whereString = whereString.replaceFirst("and", "");

		for (Field item : pTable.getFields()) {
			
			String fieldName = item.getFieldName();

			
			if (parms.containsKey(item.getFieldName()) && !isEmpty(item, parms.get(fieldName))
					|| fieldName.equals("UPDATE_TIME")) {
				sql += "[" + fieldName + "]=@" + fieldName + ",";
			}
		}
		if (sql.length() > 0) {
			sql = sql.substring(0, sql.length() - 1);
			sql = "update " + pTable.getTableName() + " set " + sql + " where " + whereString;
		}
		return sql;
	}
	
	protected static String getDeleteSql(ITable pTable) {
		Field[] keyFields = pTable.getKeyFields();
		String sql = " DELETE FROM " + pTable.getTableName() + " WHERE " + keyFields[0].getFieldName() + "=@key";
		return sql;
	}

	protected static String getInsertSql(ITable pTable, HashMap<String, Object> parms) {

		String sql = "";
		
		String fieldList = "";
		Set<String> fieldSet = new HashSet<>();
		for (Field item : pTable.getFields()) {
			String fieldName = item.getFieldName();
			fieldSet.add(fieldName);
		}
		
		for (Field item : pTable.getFields()) {

			String fieldName = item.getFieldName();
			if (parms.containsKey(item.getFieldName()) && !isEmpty(item, parms.get(fieldName))
					|| fieldName.equals("CREATE_TIME")) {
				fieldList += "[" + fieldName + "],";
				sql += "@" + fieldName + ",";
			}
		}

		if (sql.length() > 0) {
			fieldList = fieldList.substring(0, fieldList.length() - 1);
			sql = sql.substring(0, sql.length() - 1);
			sql = "Insert into " + pTable.getTableName() + " (" + fieldList + ") values (" + sql + ")";
		}
		return sql;
	}
	
	/**
	 * 插入字表数据，插入前，会删除库里已有的数据
	 * @param pTable
	 * @param parms
	 */
	private static void insertChildModel(ITable pTable,HashMap<String, Object> parms){
		
		RelationNode relationNode = pTable.getRelation();
		
		if(relationNode == null || relationNode.getChildRelationNode() == null){
			return ;
		}
		
		HashMap<String, RelationInfo> relationInfoMap = relationNode.getChildRelationNode();
		
		for(Entry<String, RelationInfo> relation : relationInfoMap.entrySet()){
			String childTableName = relation.getKey();
			ITable childTable = getTable(childTableName);
			
			RelationInfo relationiNFO = relation.getValue();
			
			String modelFieldName ;
			String foreignFieldName ;
			String modelFieldValue ;
			
			if(relationiNFO instanceof MoreToMoreRelationInfo){
				MoreToMoreRelationInfo more2MoreRelationInfo = (MoreToMoreRelationInfo)relationiNFO;
				modelFieldName = more2MoreRelationInfo.getMatserTableFieldName();
				foreignFieldName = more2MoreRelationInfo.getMiddleTableLeftFieldName();
				modelFieldValue = parms.get(modelFieldName)+"";
			}
			else{
				if(relationiNFO == null || relationiNFO.getForeignkey() == null){
					continue;
				}
				
				modelFieldName = relationiNFO.getForeignkey().keySet().iterator().next();
				foreignFieldName = relationiNFO.getForeignkey().get(modelFieldName);
				modelFieldValue = parms.get(modelFieldName)+"";
				
				if(StringUtils.IsNullOrEmpty(modelFieldValue)){
					return;
				}
			}
			
			String deleteChildSql = " DELETE FROM " + childTableName + " where "+foreignFieldName+"=@modelFieldValue";
			List<GlobalParameter> deletePc = new ArrayList<>();
			deletePc.add(new GlobalParameter("modelFieldValue", modelFieldValue));
			ExecuteUpdateSql(deleteChildSql, deletePc);
			
			int childModelIndex = 0;
			while(checkChildModelIndexExist(parms,childModelIndex,childTableName)){
				insertParameterModelIndex(childTable, parms,modelFieldValue,foreignFieldName,childModelIndex);
				childModelIndex++;
			}
		}
	}
	
	private static boolean checkChildModelIndexExist(HashMap<String, Object> parms,int index,String tableName){
		Set<String> keys = parms.keySet();
		
		for(String key : keys){
			if(key.indexOf(tableName+"_LIST["+index+"]")>-1){
				return true;
			}
		}
		return false;
	}
	
	/**
	 * 插入子表数据
	 * @param pTable
	 * @param parms
	 * @param index
	 */
	protected static void insertParameterModelIndex(ITable pTable, HashMap<String, Object> parms,String primaryTableKeyValue,String foreignFieldName,int index) {
		List<GlobalParameter> whereParameter = new ArrayList<GlobalParameter>();
		Object obj = null;
		String tableName = pTable.getTableName();
		
		String sql = "";
		String fieldList = "";

		for (Field item : pTable.getFields()) {
			String fieldName = item.getFieldName();
			
			if(fieldName.equals(foreignFieldName)){
				//如果是外键的话略过 ，下面另外加入这个值，统一处理
				continue;
			}
			
			if(fieldName.equals("GUID")){
				whereParameter.add(new GlobalParameter("GUID", UUID.randomUUID().toString()));
				fieldList += "[" + fieldName + "],";
				sql += "@" + fieldName + ",";
				continue;
			}
			
			String keyName = tableName + "_LIST["+index+"]["+fieldName+"]";
			if (parms.containsKey(keyName) && !isEmpty(item, parms.get(keyName))) {
				obj = ValueConvertUtils.ConvertValue(item.getDataType(), null, parms.get(keyName));
				whereParameter.add(new GlobalParameter(fieldName, obj, null, item.getDataType().toString()));
				fieldList += "[" + fieldName + "],";
				sql += "@" + fieldName + ",";
			}
			
		}
		
		whereParameter.add(new GlobalParameter(foreignFieldName,primaryTableKeyValue));
		fieldList += "[" + foreignFieldName + "],";
		sql += "@" + foreignFieldName + ",";
		
		if (sql.length() > 0) {
			fieldList = fieldList.substring(0, fieldList.length() - 1);
			sql = sql.substring(0, sql.length() - 1);
			sql = "Insert into " + tableName + " (" + fieldList + ") values (" + sql + ")";
		}
		
		ExecuteUpdateSql(sql, whereParameter);
	}
	
	/**
	 * 级联删除字表
	 * @param pTable
	 * @param key
	 */
	private static void deleteChildModel(ITable pTable,String key){
		
		RelationNode relationNode = pTable.getRelation();
		
		if(relationNode == null || relationNode.getChildRelationNode() == null){
			return ;
		}
		
		HashMap<String, RelationInfo> relationInfoMap = relationNode.getChildRelationNode();
		
		for(Entry<String, RelationInfo> relation : relationInfoMap.entrySet()){
			String childTableName = relation.getKey();
			
			RelationInfo relationiNFO = relation.getValue();
			if(relationiNFO == null || relationiNFO.getForeignkey() == null){
				continue;
			}
			
			String modelFieldName = relationiNFO.getForeignkey().keySet().iterator().next();
			String foreignFieldName = relationiNFO.getForeignkey().get(modelFieldName);
			String modelFieldValue = key;
			
			if(StringUtils.IsNullOrEmpty(modelFieldValue)){
				return;
			}
			
			String deleteChildSql = " DELETE FROM " + childTableName + " where "+foreignFieldName+"=@modelFieldValue";
			List<GlobalParameter> deletePc = new ArrayList<>();
			deletePc.add(new GlobalParameter("modelFieldValue", modelFieldValue));
			ExecuteUpdateSql(deleteChildSql, deletePc);
		}
	}

	protected static GlobalParameter[] getParameter(ITable pTable, HashMap<String, Object> parms,boolean isUpdate) {
		List<GlobalParameter> whereParameter = new ArrayList<GlobalParameter>();
		Object obj = null;

		for (Field item : pTable.getFields()) {
			String fieldName = item.getFieldName();
			
			if(!isUpdate && fieldName.equals("CREATE_TIME")){
				whereParameter.add(new GlobalParameter("@" + fieldName, new Date(), null, item.getDataType().toString()));
				continue;
			}
			else if(isUpdate && fieldName.equals("UPDATE_TIME")){
				whereParameter.add(new GlobalParameter("@" + fieldName, new Date(), null, item.getDataType().toString()));
				continue;
			}

			if (parms.containsKey(item.getFieldName()) && !isEmpty(item, parms.get(fieldName))) {
				obj = ValueConvertUtils.ConvertValue(item.getDataType(), null, parms.get(fieldName));
				whereParameter.add(new GlobalParameter("@" + fieldName, obj, null, item.getDataType().toString()));
			}
		}

		return whereParameter.toArray(new GlobalParameter[whereParameter.size()]);
	}
	
	private static ITable getTable(String tableName){
		ITableFactory tableFactory = (ITableFactory) SpringBeanFactory.getBean("TableFactory");
		ITable pTable = tableFactory.openTable(tableName);
		return pTable;
	}

	/**
	 * 把数据数组转成List<GlobalParameter>
	 * gislin
	 * 2016年8月15日
	 * @return
	 */
	public static List<GlobalParameter> arrayToParameters(Object[] datas,String fieldName){
		List<GlobalParameter> pc = new ArrayList<>();
		for(int i=0;i<datas.length;i++){
			pc.add(new GlobalParameter(fieldName+i, datas[i]));
		}
		return pc;
	}
	
	/**
	 * 把请求封装成rowbase
	 * @param pTable
	 * @param parms
	 * @return
	 */
	public static RowBase getModelFromParms(ITable pTable,HashMap<String, Object> parms){

		RowBase model = new RowBase();
		
		for (Field item : pTable.getFields()) {
			String fieldName = item.getFieldName();

			if (parms.containsKey(item.getFieldName()) && !isEmpty(item, parms.get(fieldName))) {
				Object obj = ValueConvertUtils.ConvertValue(item.getDataType(), null, parms.get(fieldName));
				model.setValue(fieldName, obj);
			}
		}
		
		RelationNode relationNode = pTable.getRelation();
		
		if(relationNode == null || relationNode.getChildRelationNode() == null){
			return model;
		}
		
		HashMap<String, RelationInfo> relationInfoMap = relationNode.getChildRelationNode();
		
		for(Entry<String, RelationInfo> relation : relationInfoMap.entrySet()){
			String childTableName = relation.getKey();
			ITable childTable = getTable(childTableName);
			List<RowBase> childList = new ArrayList<>();
			
			model.put(childTableName + "_LIST", childList);
			
			int childModelIndex = 0;
			while(checkChildModelIndexExist(parms,childModelIndex,childTableName)){
				RowBase childModel = new RowBase();
				childList.add(childModel);
				for (Field item : childTable.getFields()) {
					String fieldName = item.getFieldName();
					
					String keyName = childTableName + "_LIST["+childModelIndex+"]["+fieldName+"]";
					if (parms.containsKey(keyName) && !isEmpty(item, parms.get(keyName))) {
						Object obj = ValueConvertUtils.ConvertValue(item.getDataType(), null, parms.get(keyName));
						childModel.put(fieldName, obj);
					}
					
				}
				childModelIndex++;
			}
		}
		return model;
	}
	
	protected static boolean isEmpty(Field field, Object value) {

		GlobalDataType type = field.getDataType();
		switch (type) {
		case Decimal:
		case Int16:
		case Int32:
		case Int64:
		case Double:

			if (value == null || value.equals("")) {
				return true;
			} else {
				return false;
			}
		case Date:
			if (value == null || value.toString().equals("") || value.toString().equals("null")) {
				return true;
			}

			try {
				DateUtils.convertStringToDate(value.toString());
			} catch (Exception e) {
				return true;
			}

			return false;
		case DateTime:
			if (value == null || value.toString().equals("") || value.toString().equals("null")) {
				return true;
			}

			return false;

		default:
			if (value == null || value.equals("null")) {
				return true;
			} else {
				return false;
			}
		}
	}
}
